|
 |
 |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96
Hardware Considerations
When choosing hardware to use for an OLTP system, consider these several factors:
- High user load. Many concurrent processes/threads are simultaneously accessing the system.
- High I/O load. I/Os are concurrent and heavy with mostly random I/O.
- High network traffic.
Because many different processes use the machine at once, an SMP or MPP machine should scale very well. Because an SMP architecture uses CPUs based on the processes that are available to be run, if you always have a runnable process available for each CPU, you should see good scaling by adding additional processors. With an MPP machine, you see a similar effect but on a much larger scale.
Because of the many random accesses to the disks, you can benefit from a disk array. I prefer hardware striping to OS striping because hardware striping does not incur any additional overhead for the operating system and does not take up valuable CPU cycles. If hardware striping is not available, OS striping is a good alternative.
If network bandwidth is too high, consider adding additional network interface controllers (NICs) to try to reduce the bandwidth consumed on each segment. The use of stored procedures may also help reduce network traffic.
Tuning Considerations
The OLTP system is tuned to allow for many users to access the system and at the same to allow maximum throughput and minimal response times. All the tuning considerations presented in Part II of this book, Tuning the Server, apply here; there are no special tuning parameters to use in the OLTP system.
Both Oracle and the server operating system may require tuning. The following sections look first at Oracle and then at the server operating system.
Oracle Tuning
Analyze the following things carefully to determine whether adjustment to these parameters is necessary:
- DB_BLOCK_BUFFERS. This is probably the most critical area in the OLTP system. Having an insufficient number of block buffers results in higher-than-normal I/O rates and possibly an I/O bottleneck. The statistics for the buffer cache are kept in the dynamic performance table V$SYSSTAT. The ratio of PHYSICAL READS to DB BLOCK GETS and CONSISTENT GETS is the cache-miss ratio. This number should be minimized.
- Library cache. Remember to check The V$LIBRARYCACHE table, which contains statistics on how well you are using the library cache. The important columns to view in this table are PINS and RELOADS. A low number of reloads relative to the number of executions indicates a high cache-hit rate. You should be able to reduce the library cache misses by increasing the amount of memory available for the library cache. You can do this by increasing the Oracle parameter SHARED_POOL_SIZE . Depending on the size of the shared pool, you may want to increase the cache by 10 percent and see how it performs.
- Open cursors. You may also have to increase the number of cursors available for a session by increasing the Oracle parameter OPEN_CURSORS. Set the number of OPEN_CURSORS based on the application. Remember that cursors are pointers to memory used by Oracle in the processing of transactions.
- Cursor space for time. If you have plenty of memory, you may be able to speed access to the shared SQL areas by setting the Oracle initialization parameter CURSOR SPACE FOR TIME to TRUE.
- Spin counts. By tuning the Oracle initialization parameter SPIN COUNT to enable the process to spin (instead of going to sleep) while waiting for an Oracle resouce, you may see improved performance. SPIN COUNT is particularly useful when you have multiple CPUs and short-running transactions.
- Data dictionary cache. To check the efficiency of the data dictionary cache, check the dynamic performance table V$ROWCACHE. The important columns to view in this table are GETS and GETMISSES. The ratio of GETMISSES to GETS should be low.
- Rollback contention. Rollback contention occurs when too many transactions try to use the same rollback segment at the same time and some of them have to wait. You can tell if you have contention on rollback segments by examining the dynamic performance table V$WAITSTAT. Check for an excessive number of UNDO HEADERs, UNDO BLOCKs, SYSTEM UNDO HEADERs, and SYSTEM UNDO BLOCKs. Compare these values to the total number of requests for data. If the number is high, you need more rollback segments.
- Use many small rollback segments for OLTP. Create many small rollback segments of perhaps 10K to 20K in size, with two to four extents each; perhaps you can have a rollback segment available for each server process.
- Latch contention. You can determine latch contention by examining the dynamic performance table V$LATCH. Look for the ratio of MISSES to GETS, the number of SLEEPS, and the ratio of IMMEDIATE MISSES to IMMEDIATE GETS. If the miss ratio is high, reduce the size of LOG_SMALL_ENTRY_MAX_SIZE to minimize the time any one user process holds the latch; alternatively, increase the value of LOG_SIMULTANEOUS_COPIES to reduce contention by adding more redo copy latches. If neither of these parameters help, set the initialization parameter LOG_ENTRY_PREBUILD_THRESHOLD . Any redo entry of a size smaller than the size you set this parameter must be prebuilt. This reduces the time the latch is held.
- Checkpoints. It may be necessary to tune checkpoints under certain circumstances. Although this is usually not necessary, if you see severely degraded performance during checkpoints, you can reduce the effect by enabling the CKPT process. Do so by setting the Oracle initialization parameter CHECKPOINT_PROCESS to TRUE.
- Archiving. By now, you should be convinced that you should always run with archiving enabled. By adjusting the initialization parameters LOG_ARCHIVE_BUFFERS and LOG_ARCHIVE_BUFFER_SIZE , you can either slow down or speed up the performance of archiving. By speeding up archiving, the effect on the system is of a shorter duration but is more noticeable. Slowing down archiving lengthens the duration but reduces the effect.
These are some of the areas to which you should pay particular attention when tuning a system used primarily for OLTP. The areas that probably require the most attention are I/O and memory because they are so closely related. By optimizing the use of memory, you may be able to reduce I/Os, which are probably running near the hardware limitations.
|